package swd.basic.table_schema_maintain;

import com.pub.database.*;
import java.util.*;

public class Business {
   QueryBean gqbnDAO=null;
   public Business(QueryBean pqbnDAO){
      gqbnDAO=pqbnDAO;
   }
   String selectTableDataToFld(View view) throws Exception {
      String lstrSQL=null;
      Hashtable<String,Object> lhtTableData=null;
      try{
         lstrSQL="select wide,seqno,dbf_no,fld_no,is_key,fld_kind,fld_name,wide_dec,editmail_yn from wkf_layout where dbf_no='" + view.getdbf_no() + "'" + " and fld_no='" + view.getfld_no() + "'";
         lhtTableData=gqbnDAO.readSingleRowData(lstrSQL);
         if(lhtTableData==null){
            return "查無資料";
         }
         view.setwide(String.valueOf((lhtTableData.get("WIDE").toString())));
         view.setseqno(String.valueOf((lhtTableData.get("SEQNO").toString())));
         view.setdbf_no(String.valueOf((lhtTableData.get("DBF_NO").toString())));
         view.setfld_no(String.valueOf((lhtTableData.get("FLD_NO").toString())));
         view.setis_key(String.valueOf((lhtTableData.get("IS_KEY").toString())));
         view.setfld_kind(String.valueOf((lhtTableData.get("FLD_KIND").toString())));
         view.setfld_name(String.valueOf((lhtTableData.get("FLD_NAME").toString())));
         view.setwide_dec(String.valueOf((lhtTableData.get("WIDE_DEC").toString())));
         view.seteditmail_yn(String.valueOf((lhtTableData.get("EDITMAIL_YN").toString())));

         return "";
      }
      catch(Exception e){
         throw new Exception(e.toString());
      }
      finally{
         lhtTableData.clear();
         lhtTableData=null;
      }
   }
   String exeUpdSQL(View view) throws Exception{
      String lstrSQL=new String();
      Vector<Object> lvtrParam = new Vector<Object>();
      try{
         lvtrParam.add(view.getwide()==null ? null :String.valueOf(view.getwide()));
         lvtrParam.add(view.getseqno()==null ? null :String.valueOf(view.getseqno()));
         lvtrParam.add(view.getdbf_no()==null ? null :String.valueOf(view.getdbf_no()));
         lvtrParam.add(view.getfld_no()==null ? null :String.valueOf(view.getfld_no()));
         lvtrParam.add(view.getis_key()==null ? null :String.valueOf(view.getis_key()));
         lvtrParam.add(view.getfld_kind()==null ? null :String.valueOf(view.getfld_kind()));
         lvtrParam.add(view.getfld_name()==null ? null :String.valueOf(view.getfld_name()));
         lvtrParam.add(view.getwide_dec()==null ? null :String.valueOf(view.getwide_dec()));
         lvtrParam.add(view.geteditmail_yn()==null ? null :String.valueOf(view.geteditmail_yn()));
         lstrSQL="update wkf_layout set wide=?,seqno=?,dbf_no=?,fld_no=?,is_key=?,fld_kind=?,fld_name=?,wide_dec=?,editmail_yn=? where dbf_no='" + view.getdbf_no() + "'" + " and fld_no='" + view.getfld_no() + "'";
         if(!gqbnDAO.executeSQL(lstrSQL,lvtrParam)){
            return "wkf_layout";
         }
         return "";
      }
      catch(Exception e){
         e.printStackTrace();
         throw new Exception(e.toString());
      }
      finally{
         lvtrParam.clear();
      }
   }
   String exeInsSQL(View view) throws Exception{
      String lstrSQL=new String();
      Vector<Object> lvtrParam = new Vector<Object>();
      try{
         lvtrParam.add((view.getdbf_no()==null ? null : view.getdbf_no()));
         lvtrParam.add((view.getseqno()==null ? null : view.getseqno()));
         lvtrParam.add((view.getfld_no()==null ? null : view.getfld_no()));
         lvtrParam.add((view.getfld_name()==null ? null : view.getfld_name()));
         lvtrParam.add((view.getfld_kind()==null ? null : view.getfld_kind()));
         lvtrParam.add((view.getwide()==null ? null : view.getwide()));
         lvtrParam.add((view.getwide_dec()==null ? null : view.getwide_dec()));
         lvtrParam.add((view.getis_key()==null ? null : view.getis_key()));
         lvtrParam.add((view.geteditmail_yn()==null ? null : view.geteditmail_yn()));
         lstrSQL="insert into wkf_layout(dbf_no,seqno,fld_no,fld_name,fld_kind,wide,wide_dec,is_key,editmail_yn) values (?,?,?,?,?,?,?,?,?)";
         if(!gqbnDAO.executeSQL(lstrSQL,lvtrParam)){;
            return "wkf_layout";
         }
         lvtrParam.clear();

         return "";
      }
      catch(Exception e){
         e.printStackTrace();
         throw new Exception(e);
      }
      finally{
         lvtrParam.clear();
      }
   }

   boolean exeUpdSQL(View view,int pintSn){
      String lstrSQL=new String();
      Vector<Object> lvtrParam = new Vector<Object>();
      try{
         lvtrParam.add(view.getwide_g()[pintSn]==null ? null :String.valueOf(view.getwide_g()[pintSn]));
         lvtrParam.add(view.getseqno_g()[pintSn]==null ? null :String.valueOf(view.getseqno_g()[pintSn]));
         lvtrParam.add(view.getdbf_no_g()[pintSn]==null ? null :String.valueOf(view.getdbf_no_g()[pintSn]));
         lvtrParam.add(view.getfld_no_g()[pintSn]==null ? null :String.valueOf(view.getfld_no_g()[pintSn]));
         lvtrParam.add(view.getis_key_g()[pintSn]==null ? null :String.valueOf(view.getis_key_g()[pintSn]));
         lvtrParam.add(view.getfld_kind_g()[pintSn]==null ? null :String.valueOf(view.getfld_kind_g()[pintSn]));
         lvtrParam.add(view.getfld_name_g()[pintSn]==null ? null :String.valueOf(view.getfld_name_g()[pintSn]));
         lvtrParam.add(view.getwide_dec_g()[pintSn]==null ? null :String.valueOf(view.getwide_dec_g()[pintSn]));
         lstrSQL="update wkf_layout " +
                 "   set wide=?,seqno=?,dbf_no=?,fld_no=?,is_key=?," +
                 "       fld_kind=?,fld_name=?,wide_dec=?" +
                 " where dbf_no='" + view.getdbf_no_g()[pintSn] + "'" +
                 "   and fld_no='" + view.getfld_no_old_g()[pintSn] + "'";

         return gqbnDAO.executeSQL(lstrSQL,lvtrParam);
      }
      catch(Exception e){
         e.printStackTrace();
         return false;
      }
      finally{
         lvtrParam.clear();
      }
   }

   boolean exeInsSQL(View view,int pintSn){
      String lstrSQL=new String();
      Vector<Object> lvtrParam = new Vector<Object>();
      try{
         lvtrParam.add((view.getdbf_no_g()[pintSn]==null ? null : view.getdbf_no_g()[pintSn]));
         lvtrParam.add((view.getseqno_g()[pintSn]==null ? null : view.getseqno_g()[pintSn]));
         lvtrParam.add((view.getfld_no_g()[pintSn]==null ? null : view.getfld_no_g()[pintSn]));
         lvtrParam.add((view.getfld_name_g()[pintSn]==null ? null : view.getfld_name_g()[pintSn]));
         lvtrParam.add((view.getfld_kind_g()[pintSn]==null ? null : view.getfld_kind_g()[pintSn]));
         lvtrParam.add((view.getwide_g()[pintSn]==null ? null : view.getwide_g()[pintSn]));
         lvtrParam.add((view.getwide_dec_g()[pintSn]==null ? null : view.getwide_dec_g()[pintSn]));
         lvtrParam.add((view.getis_key_g()[pintSn]==null ? null : view.getis_key_g()[pintSn]));
         lstrSQL="insert into wkf_layout" +
                 " (dbf_no,seqno,fld_no,fld_name,fld_kind,wide,wide_dec,is_key)" +
                 " values (?,?,?,?,?,?,?,?)";

         gqbnDAO.executeSQL(lstrSQL,lvtrParam);

         return true;
      }
      catch(Exception e){
         e.printStackTrace();
         return false;
      }
      finally{
         lvtrParam.clear();
      }
   }



   String batchUpdateData(View view) throws Exception{
       // 整批存檔
       String[] lstrRowSn=view.getrowsn().split(",");
       int intRowCount=0;

       try{
           // 先存修改的資料
           for(int i=0;i<lstrRowSn.length;i++){
               if (view.getinsert_yn_g()[Integer.parseInt(lstrRowSn[i])].equals("N")){
                  if(!exeUpdSQL(view,Integer.parseInt(lstrRowSn[i]))){
                      throw new Exception("update error");
                  }
               }
           }

           // 再存新增的資料
           intRowCount=Integer.parseInt(view.getdataviewtotalcount());
           for(int i=0;i<intRowCount;i++){
               if (view.getinsert_yn_g()[i].equals("Y")){
                   if(!exeInsSQL(view,i)){
                       throw new Exception("Insert error");
                   }
               }
           }

           return "儲存成功!";
       }
       catch(Exception e){
           return "儲存失敗!!";
       }
   }

   void exeDelSQL(View view,int pintSn){
      String lstrSQL=new String();
      try{
         lstrSQL="delete wkf_layout " +
                 " where dbf_no='" + view.getdbf_no_g()[pintSn] + "'" +
                 "   and fld_no='" + view.getfld_no_old_g()[pintSn] + "'";
         gqbnDAO.executeSQL(lstrSQL);
      } catch(Exception e){
         e.printStackTrace();
      }
   }

   void setDataViewForm(DataViewForm DataViewForm,View view,String pstrLoginUser) throws Exception{

      //StringBuffer stbCondition = new StringBuffer();
      StringBuffer stbPageActionURI = new StringBuffer("table_schema_maintain.jsp?rowsn=");

      DataViewForm.setFieldsList(" * ");
      DataViewForm.setTablesList(" (select '',seqno,fld_no,fld_name,fld_kind,wide,wide_dec,is_key,dbf_no,'N' insert_yn" +
                                 "    from wkf_layout" +
                                 "   where dbf_no='" + (view.getdbf_no()==null ? "" : view.getdbf_no()) + "' " +
                                 getSelectSqlData(view) + ") ");
      DataViewForm.setSQLCondition("1=1");


      /*DataViewForm.setFieldsList(" '',seqno,fld_no,fld_name,fld_kind,wide,wide_dec,is_key,dbf_no,'N' insert_yn");
      DataViewForm.setTablesList(" wkf_layout ");
      DataViewForm.setSQLCondition("dbf_no = '" + (view.getdbf_no()==null ? "" : view.getdbf_no()) + "'");*/
      DataViewForm.setOrderByFields("seqno");
      DataViewForm.setOrderBySeq("asc");
      DataViewForm.setGroupBy(null);
      DataViewForm.setHaving(null);

      String strColRelation[][] = new String[11][];

      strColRelation[0] = new String[5];
      strColRelation[1] = new String[5];
      strColRelation[2] = new String[5];
      strColRelation[3] = new String[5];
      strColRelation[4] = new String[5];
      strColRelation[5] = new String[5];
      strColRelation[6] = new String[5];
      strColRelation[7] = new String[5];
      strColRelation[8] = new String[5];
      strColRelation[9] = new String[5];
      strColRelation[10] = new String[5];

      strColRelation[0][0] = "''";
      strColRelation[0][1] = "INPUT";
      strColRelation[0][2] = "WIDTH='10%' align='center'";
      strColRelation[0][3] = "<INPUT class='button b_normal' TYPE=\"BUTTON\" onclick=\"javascript:delRowSn('%sn%');document.all.item('prgAction').value='delete';document.forms[0].submit();\" NAME=\"del\" VALUE=";
      strColRelation[0][4] = "'刪除'";

      strColRelation[1][0]="'序號'";
      strColRelation[1][1]="INPUT";
      strColRelation[1][2]="WIDTH='50%'";
      strColRelation[1][3]="<INPUT TYPE='text' size='4' MAXLENGTH='4' name='seqno_g' onchange=\"javascript:saveRowSn('%sn%');\" CLASS='input' VALUE=";
      strColRelation[1][4]="1";

      strColRelation[2][0]="'<IMG SRC=\"/PUBPAGE/pub_images/star.gif\" WIDTH=\"10\" HEIGHT=\"10\" HSPACE=\"2\" alt=\"\"/>欄位名稱'";
      strColRelation[2][1]="INPUT";
      strColRelation[2][2]="WIDTH='50%'";
      strColRelation[2][3]="<INPUT TYPE='text' size='35' MAXLENGTH='40' name='fld_no_g' onchange=\"javascript:saveRowSn('%sn%');checkTheSameFldNo(this,'%sn%');\" CLASS='input' VALUE=";
      strColRelation[2][4]="2";

      strColRelation[3][0]="'欄位說明'";
      strColRelation[3][1]="INPUT";
      strColRelation[3][2]="WIDTH='50%'";
      strColRelation[3][3]="<INPUT TYPE='text' size='35' MAXLENGTH='100' name='fld_name_g' onchange=\"javascript:saveRowSn('%sn%');\" CLASS='input' VALUE=";
      strColRelation[3][4]="3";

      strColRelation[4][0]="'欄位種類'";
      strColRelation[4][1]="SELECT";
      strColRelation[4][2]="WIDTH='10%' align='center'";
      strColRelation[4][3]="<SELECT CLASS='select' NAME='fld_kind_g' onchange=\"javascript:saveRowSn('%sn%');\">";
      strColRelation[4][4]="<option value=\"C\">文字</option>," +
                           "<option value=\"N\">數字</option>," +
                           "<option value=\"D\">日期</option>," +
                           "<option value=\"L\">LONGRAW</option>";

      strColRelation[5][0]="'欄位長度'";
      strColRelation[5][1]="INPUT";
      strColRelation[5][2]="WIDTH='10%'";
      strColRelation[5][3]="<INPUT TYPE='text' size='4' MAXLENGTH='4' name='wide_g'  onDblClick=\"javascript:fc_get_sys_data(this);saveRowSn('%sn%');\" onchange=\"javascript:saveRowSn('%sn%');\" CLASS='input' VALUE=";
      strColRelation[5][4]="5";

      strColRelation[6][0]="'小數長度'";
      strColRelation[6][1]="INPUT";
      strColRelation[6][2]="WIDTH='10%'";
      strColRelation[6][3]="<INPUT TYPE='text' size='4' MAXLENGTH='4' name='wide_dec_g'  onDblClick=\"javascript:fc_get_sys_data(this);saveRowSn('%sn%');\" onchange=\"javascript:saveRowSn('%sn%');\" CLASS='input' VALUE=";
      strColRelation[6][4]="6";

      strColRelation[7][0]="'主鍵'";
      strColRelation[7][1]="SELECT";
      strColRelation[7][2]="WIDTH='10%' align='center'";
      strColRelation[7][3]="<SELECT CLASS='select' NAME='is_key_g' onchange=\"javascript:saveRowSn('%sn%');\">";
      strColRelation[7][4]="<option value=\"Y\">是</option>," +
                           "<option value=\"N\">否</option>";

      strColRelation[8][0]="HIDDEN";
      strColRelation[8][1]="INPUT";
      strColRelation[8][2]="WIDTH='0%'";
      strColRelation[8][3]="<INPUT TYPE='HIDDEN' size='8' name='dbf_no_g' VALUE=";
      strColRelation[8][4]="8";

      strColRelation[9][0]="HIDDEN";
      strColRelation[9][1]="INPUT";
      strColRelation[9][2]="WIDTH='0%'";
      strColRelation[9][3]="<INPUT TYPE='HIDDEN' size='8' name='insert_yn_g' VALUE=";
      strColRelation[9][4]="9";

      strColRelation[10][0]="HIDDEN";
      strColRelation[10][1]="INPUT";
      strColRelation[10][2]="WIDTH='0%'";
      strColRelation[10][3]="<INPUT TYPE='HIDDEN' size='35' MAXLENGTH='40' name='fld_no_old_g' VALUE=";
      strColRelation[10][4]="2";

      DataViewForm.setColRelation(strColRelation);//將欄位相關設定殖入ActionForm
      DataViewForm.setJumpPageButtonShow(false);   //是否要秀出上下頁按鈕
      DataViewForm.setPageBtnOnTop(true);         //將button and rows info 秀在頂端
      DataViewForm.setPageBtnOnButtom(false);      //將button and rows info 秀在底端
      DataViewForm.setFilterShow(false);           //是否要秀出過濾選擇欄位
      DataViewForm.setJumpPageShow(false);         //是否要秀出跳頁下拉式選單
      DataViewForm.setRowsInfoShow(false);         //是否要秀出筆數資訊
      DataViewForm.setRowPageShow(true);          //是否要秀出每頁筆數控制欄
      DataViewForm.setExcelShow(false);           //是否要秀出轉Excel button
      DataViewForm.setOrderEnable(false);          //是否要提供排序點選功能
      DataViewForm.setShowHeaderWhenNoData(false);//當ResultSet無資料是否要秀Header

      DataViewForm.setTableHeaderHtml("class='td_head_grid' align='center'  width='680px'");
          //設定上一頁與下一頁按鈕的form action(通常為母頁即本頁)
      DataViewForm.setPageActionURI(stbPageActionURI.toString());
   }

   String checkNotNullFld(View view) throws Exception{
      StringBuffer lstbNullFldList=new StringBuffer();
      lstbNullFldList.append(view.getdbf_no()==null ? view.getdbf_noTitle() + "," : "");
      lstbNullFldList.append(view.getfld_kind()==null ? view.getfld_kindTitle() + "," : "");
      lstbNullFldList.append(view.getfld_name()==null ? view.getfld_nameTitle() + "," : "");
      lstbNullFldList.append(view.getfld_no()==null ? view.getfld_noTitle() + "," : "");
      lstbNullFldList.append(view.getis_key()==null ? view.getis_keyTitle() + "," : "");
      lstbNullFldList.append(view.getwide()==null ? view.getwideTitle() + "," : "");
      return (lstbNullFldList.toString().equals("") ? "" : lstbNullFldList.delete(lstbNullFldList.length()-1,lstbNullFldList.length()).toString());
   }

   String getOldInsertData(View view){
      // 取得已輸入但未存檔的資料(只有新增的部份 insert_yn = Y)
      int intRowCount=0;
      String strReturn="";

      if (view.getdataviewtotalcount()==null||view.getdataviewtotalcount().equals("null")){
         strReturn="";
      }else {
         if (view.getdataviewtotalcount().equals("0")){return "";}

         intRowCount=Integer.parseInt(view.getdataviewtotalcount());

         for(int i=0;i<intRowCount;i++){
            if (view.getinsert_yn_g()[i].equals("Y")){
               strReturn += " union all select '','" + view.getseqno_g()[i] + "','" + view.getfld_no_g()[i] + "'," +
                                               "'" + view.getfld_name_g()[i] + "','" + view.getfld_kind_g()[i] + "'," +
                                               "'" + view.getwide_g()[i] + "','" + view.getwide_dec_g()[i] + "'," +
                                               "'" + view.getis_key_g()[i] + "','" + view.getdbf_no() + "','Y' from dual ";
            }
         }
      }

      return strReturn;
   }

   String getSelectSqlData(View view){
      // 取得Select SQL
      StringBuffer stbSQL = new StringBuffer();
      int intInsertCount=0;
      if (view.getinsertcount()==null||view.getinsertcount().equals("null")){
         intInsertCount=0;
      } else {
         intInsertCount=Integer.parseInt(view.getinsertcount());
      }
      stbSQL.append(getOldInsertData(view));

      if (view.getImport_yn()!=null && view.getImport_yn().equals("Y")){
          stbSQL.append(" union select '',seqno,fld_no,fld_name,fld_kind,wide,wide_dec,is_key,'"+view.getdbf_no()+"','Y' insert_yn from wkf_layout where dbf_no='SWD'");
          intInsertCount = intInsertCount -1;
      }
      for (int i=0;i<intInsertCount;i++){
          stbSQL.append(" union all select '','0000','','','','','','','");
          stbSQL.append(view.getdbf_no());
          stbSQL.append("','Y' from dual");
      }

      return stbSQL.toString();
   }
   void setInitValueForAdd(View view,String pstrUserID) throws Exception{
       // 目前有幾筆新增的資料為新增的
          view.setinsertcount("1");
   }

   void setReadonlyForAdd(View view) throws Exception{
       view.setdbf_no_readonly(true);
   }
   void saveAllData(View view) throws Exception{
    String strDelSn = view.getrowsn();
    int iRowCount = 0;
    String strAllSn = "";
    try{
        if (view.getdataviewtotalcount() != null) {
            iRowCount = Integer.parseInt(view.getdataviewtotalcount());
            for (int i = 0; i < iRowCount; i++) {
                strAllSn = strAllSn + i + ",";
            } //for
            view.setrowsn(strAllSn);
            batchUpdateData(view);
            view.setdataviewtotalcount("0");
        }
        view.setrowsn(strDelSn);
    }catch(Exception e){
        e.printStackTrace();
    }
   }
}
